-- 拼卡鸭数据库表结构
-- 创建时间: 2024年

-- 1. 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    openid VARCHAR(100) UNIQUE NOT NULL,
    nickname VARCHAR(100),
    avatar_url TEXT,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 店铺表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    address TEXT,
    phone VARCHAR(20),
    business_hours VARCHAR(100),
    latitude DECIMAL(10, 8), -- 纬度
    longitude DECIMAL(11, 8), -- 经度
    logo_url TEXT, -- 店铺logo
    status SMALLINT DEFAULT 1, -- 1:正常 0:停业
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    store_id INTEGER REFERENCES stores(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    original_price DECIMAL(10,2) NOT NULL,
    group_buy_price DECIMAL(10,2) NOT NULL,
    group_buy_shares INTEGER DEFAULT 10, -- 拼购份数
    group_buy_duration INTEGER DEFAULT 86400, -- 拼购时间(秒)
    images TEXT[], -- 商品图片数组
    status SMALLINT DEFAULT 1, -- 1:上架 0:下架
    valid_days INTEGER, -- 有效期(天)
    category VARCHAR(100), -- 分类
    tags TEXT[], -- 标签
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. 拼购订单表
CREATE TABLE group_buy_orders (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    total_shares INTEGER NOT NULL, -- 总份数
    sold_shares INTEGER DEFAULT 0, -- 已售份数
    status SMALLINT DEFAULT 1, -- 1:拼购中 2:拼购成功 3:拼购失败
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. 用户订单表
CREATE TABLE user_orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    group_buy_order_id INTEGER REFERENCES group_buy_orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    order_no VARCHAR(50) UNIQUE NOT NULL, -- 订单号
    shares INTEGER NOT NULL, -- 购买份数
    total_amount DECIMAL(10,2) NOT NULL,
    payment_status SMALLINT DEFAULT 0, -- 0:待支付 1:已支付 2:已退款
    payment_time TIMESTAMP,
    refund_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 6. 储值卡表
CREATE TABLE stored_value_cards (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    store_id INTEGER REFERENCES stores(id) ON DELETE CASCADE,
    card_number VARCHAR(50) UNIQUE NOT NULL,
    original_amount DECIMAL(10,2) NOT NULL,
    current_amount DECIMAL(10,2) NOT NULL,
    status SMALLINT DEFAULT 1, -- 1:正常 0:已用完
    valid_until DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 7. 消费记录表
CREATE TABLE consumption_records (
    id SERIAL PRIMARY KEY,
    card_id INTEGER REFERENCES stored_value_cards(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    store_id INTEGER REFERENCES stores(id) ON DELETE CASCADE,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 8. 系统配置表
CREATE TABLE system_configs (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_openid ON users(openid);
CREATE INDEX idx_products_store_id ON products(store_id);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_group_buy_orders_product_id ON group_buy_orders(product_id);
CREATE INDEX idx_group_buy_orders_status ON group_buy_orders(status);
CREATE INDEX idx_user_orders_user_id ON user_orders(user_id);
CREATE INDEX idx_user_orders_order_no ON user_orders(order_no);
CREATE INDEX idx_user_orders_payment_status ON user_orders(payment_status);
CREATE INDEX idx_stored_value_cards_user_id ON stored_value_cards(user_id);
CREATE INDEX idx_stored_value_cards_card_number ON stored_value_cards(card_number);
CREATE INDEX idx_consumption_records_card_id ON consumption_records(card_id);

-- 插入默认系统配置
INSERT INTO system_configs (config_key, config_value, description) VALUES
('default_group_buy_shares', '10', '默认拼购份数'),
('default_group_buy_duration', '86400', '默认拼购时间(秒)'),
('min_group_buy_shares', '1', '最小拼购份数'),
('max_group_buy_shares', '10', '最大拼购份数'),
('group_buy_success_threshold', '10', '拼购成功阈值');

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新updated_at的表创建触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_group_buy_orders_updated_at BEFORE UPDATE ON group_buy_orders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_orders_updated_at BEFORE UPDATE ON user_orders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_stored_value_cards_updated_at BEFORE UPDATE ON stored_value_cards FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_system_configs_updated_at BEFORE UPDATE ON system_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 